"""
SqlAlchermy join实现复杂查询
1. join：默认inner join内连接
"""
import os
import sys

path = os.path.dirname(__file__)
sys.path.append(os.path.join(path, '..'))

import random
from faker import Faker
from sqlalchemy import Column, Integer, Text, String, ForeignKey, func
from sqlalchemy.orm import backref, relationship
from conf.mysql_config import SESSION, BASE


class StoryAuthorOrm(BASE):
    __tablename__ = 'story_author'
    id = Column(Integer, autoincrement=True, primary_key=True)
    name = Column(String(30), comment='作者姓名', doc={'zh': '作者姓名'})

    def __str__(self):
        return f'<StoryAuthorOrm id={self.id}, name={self.name}>'

    __repr__ = __str__


class StoryOrm(BASE):
    __tablename__ = 'story'
    id = Column(Integer, autoincrement=True, primary_key=True)
    title = Column(String(250), comment='故事标题', doc={'zh': '故事标题'})
    content = Column(Text, comment='故事内容', doc={'zh': '故事内容'})
    author_id = Column(Integer, ForeignKey('story_author.id'))
    author = relationship('StoryAuthorOrm', backref=backref('stories', uselist=True))

    def __str__(self):
        return f'<StoryOrm id={self.id}, title={self.title}>'

    __repr__ = __str__


BASE.metadata.create_all()


def add_data():
    fake = Faker(locale='zh_CN')
    authors = []
    for i in range(5):
        author = StoryAuthorOrm(name=fake.name())
        if i <= 2:
            stories = [StoryOrm(title=fake.sentence(), content='\n'.join(fake.sentences()))
                       for _ in range(random.randint(1, 10))]
            author.stories = stories
        authors.append(author)
    SESSION.add_all(authors)
    SESSION.commit()


def search_data():
    # join：默认inner join内连接
    results = SESSION.query(StoryAuthorOrm.name, func.count(StoryOrm.id)).join(StoryOrm).group_by(
        StoryAuthorOrm.id).order_by(func.count(StoryOrm.id).desc()).all()
    print(results)
    # outer join：指定left join或者是right join
    results = SESSION.query(StoryAuthorOrm.name, func.count(StoryOrm.id)).outerjoin(
        StoryOrm, StoryOrm.author_id == StoryAuthorOrm.id).group_by(
        StoryAuthorOrm.id).order_by(func.count(StoryOrm.id).desc()).all()
    print(results)


if __name__ == '__main__':
    search_data()
